package cn.xuqiudong.attachment.dao.mysql;

import cn.xuqiudong.attachment.dao.BaseAttachmentRelationDao;
import cn.xuqiudong.attachment.model.AttachmentRelation;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import javax.sql.DataSource;
import java.sql.*;
import java.util.List;

/**
 * 描述:基于mysql的附件关系的操作
 * id自增， 创建时间和修改时间数据库自动生成
 * @author Vic.xu
 * @since 2022-07-07 11:33
 */
public class MysqlAttachmentRelationDao extends BaseAttachmentRelationDao {

    /**
     * 插入sql
     */
    private String inserSql = "insert into attachment_relation(business_id, attachment_id, table_name, column_name, create_id, update_id) values(?,?,?,?,?,?)";

    /**
     * 查询sql
     */
    private String querySql = "select a.`id`, a.`attachment_id` as attachmentId,a.`business_id` as businessId, a.`column_name` as columnName, a.`create_id` as createId,  " +
            " a.`is_delete` as `delete`, a.`is_enable` as `enable`, a.`update_id` as updateId, a.`create_time` as createTime, a.`update_time` as updateTime, " +
            " a.`update_id` as updateId, a.`version`, b.`original_name` as originalName, a.`table_name` AS tableName " +
            " from attachment_relation a left join attachment b on a.`attachment_id` = b.`id` " +
            " where 1=1 ";

    @Override
    public void insert(AttachmentRelation relation) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        Integer userId = getCurrentUserId();
        jdbcTemplate.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(inserSql, Statement.RETURN_GENERATED_KEYS);
                setInsertSqlParams(userId, ps, relation);
                return ps;
            }
        }, keyHolder);
        relation.setId(keyHolder.getKey().intValue());
    }

    @Override
    public void batchInsert(List<AttachmentRelation> relationList) {
        if (CollectionUtils.isEmpty(relationList)) {
            return;
        }
        Integer userId = getCurrentUserId();
        //jdbcTemplate  不支持批量新增返回id
        DataSource dataSource = jdbcTemplate.getDataSource();
        Connection connection = DataSourceUtils.getConnection(dataSource);
        try {
            connection.setAutoCommit(false);
            PreparedStatement ps = connection.prepareStatement(inserSql, Statement.RETURN_GENERATED_KEYS);
            for (AttachmentRelation relation : relationList) {
                setInsertSqlParams(userId, ps, relation);
                ps.addBatch();
            }
            ps.executeBatch();
            connection.commit();
            //获取id结果  并设置到对象中去
            ResultSet rs = ps.getGeneratedKeys();
            int index = 0;
            while (rs.next()) {
                relationList.get(index++).setId(rs.getInt(1));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataSourceUtils.releaseConnection(connection, dataSource);
        }

    }

    /**
     * 设置insert的参数
     * @param userId user id
     * @param ps PreparedStatement
     * @param relation 附件关系对象
     * @throws SQLException ex
     */
    private void setInsertSqlParams(Integer userId, PreparedStatement ps, AttachmentRelation relation) throws SQLException {
        ps.setString(1, relation.getBusinessId());
        ps.setInt(2, relation.getAttachmentId());
        ps.setString(3, relation.getTableName());
        ps.setString(4, relation.getColumnName());
        ps.setInt(5, userId);
        ps.setInt(6, userId);
    }

    @Override
    public void delete(Integer... ids) {
        if (ids == null || ids.length == 0) {
            return;
        }
        String deleteSql = "delete from attachment_relation a where a.id = ? ";
        jdbcTemplate.batchUpdate(deleteSql, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setInt(1, ids[i]);
            }

            @Override
            public int getBatchSize() {
                return ids.length;
            }
        });
    }

    @Override
    public int delete(String businessId, String table) {
        return jdbcTemplate.update("DELETE FROM attachment_relation a WHERE a.`business_id` = ? AND a.`table_name` = ?", businessId, table);
    }

    @Override
    public List<AttachmentRelation> attachmentRelations(String businessId, String table) {
        String sql = querySql + " and a.`business_id` = ?  and a.`table_name` = ? order by a.id desc ";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<AttachmentRelation>(AttachmentRelation.class) {
        }, businessId, table);
    }

    @Override
    public List<AttachmentRelation> attachmentRelations(String businessId, String table, String column) {
        String sql = querySql + " and a.`business_id` = ?  and a.`table_name` = ? and a.`column_name` = ? order by a.id desc ";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<AttachmentRelation>(AttachmentRelation.class) {
        }, businessId, table, column);
    }


}
